package avicit.bdp.lineage.utils;

import avicit.bdp.lineage.druid.LineageSqlUtils;
import avicit.bdp.lineage.druid.util.LineageContext;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.io.FileUtil;
import com.alibaba.druid.DbType;

import java.io.UnsupportedEncodingException;


public class MainTest {
    public static final String INSERT_STMT_SQL =
            "INSERT INTO TABLE db_test.table_result "
                    + "SELECT t1.id, t2.name "
                    + "FROM ( SELECT id1 + id2 AS id FROM db_test.table1 ) t1 "
                    + "LEFT JOIN ( SELECT id, name "
                    + "FROM ( SELECT id, my_test(source_name) AS name FROM db_test.table2 ) aa ) t2 "
                    + "ON t1.id=t2.id";

    public static final String CREATE_SQL =
            "CREATE TABLE db1.table1 (id number comment 'id', "
                    + "name string comment '姓名', "
                    + "age number)";

    public static final String INSERT_STATEMENT_SQL1 =
            "INSERT INTO suppliers ("
                    + "supplierName, "
                    + "phone, "
                    + "addressLine1,"
                    + "addressLine2,"
                    + "city,"
                    + "state,"
                    + "postalCode,"
                    + "country,"
                    + "customerNumber"
                    + ")"
                    + " SELECT"
                    + "  customerName,"
                    + "  phone,"
                    + "  addressLine1,"
                    + "  addressLine2,"
                    + "  city,"
                    + "  state ,"
                    + "  postalCode,"
                    + "  country,"
                    + "  customerNumber"
                    + " FROM customers"
                    + " WHERE country = 'USA' "
                    + " AND state = 'CA'";

    public static final String INSERT_INTO_SELECT_SQL1 =
            "INSERT INTO suppliers (id,name) "
                    + "SELECT t1.id, t2.name "
                    + "FROM ( SELECT id1 + id2 AS id FROM db_test.table1 ) t1 "
                    + "LEFT JOIN ( SELECT id, name "
                    + "FROM ( SELECT id, my_test(source_name) AS name FROM db_test.table2 ) ) t2 "
                    + "ON t1.id=t2.id;";


    public static final String CREATE_TABLE_COMPLEX_SQL =
            "CREATE TABLE tmp.tmp_a_supp_achievement_an_mom_001 AS\n"
                    + "  SELECT a1.dim_day_txdate,\n"
                    + "         a.a_pin,\n"
                    + "         Sum(Coalesce(b.amount, 0)) AS total_amount\n"
                    + "         , Sum(Coalesce(c.refund_amt, 0)) AS refund_amt\n"
                    + "         , Sum(os_prcp_amt) os_prcp_amt\n"
                    + "  FROM (SELECT dim_day_txdate \n"
                    + "        FROM dmv.dim_day\n"
                    + "        WHERE dim_day_txdate>=concat(cast(Year('2018-05-15')-1 AS string),'-', substring('2018-05-15', 6, 2), '-01')\n"
                    + "          AND dim_day_txdate<='2018-05-15' )a1\n"
                    + "  JOIN (SELECT DISTINCT a_pin, product_type\n"
                    + "        FROM dwd.dwd_as_qy_cust_account_s_d\n"
                    + "        WHERE dt ='2018-05-15' AND product_type='20288' )a\n"
                    + "  LEFT OUTER JOIN (SELECT substring(tx_time, 1, 10) AS time, sum(order_amt) AS amount, a_pin\n"
                    + "                    FROM dwd.dwd_actv_as_qy_iou_receipt_s_d\n"
                    + "                    WHERE a_order_type='20096' AND a_pin NOT IN ('vep_test', 'VOPVSP测试')\n"
                    + "                      AND dt='2018-05-15'\n"
                    + "                    GROUP BY substring(tx_time, 1, 10), a_pin )b\n"
                    + "              ON cast(a.a_pin AS string)=cast(b.a_pin AS string) AND a1.dim_day_txdate=b.time\n"
                    + "  LEFT OUTER JOIN ( SELECT substring(refund_time, 1, 10) AS refund_time, a_pin, sum(refund_amt)AS refund_amt\n"
                    + "                    FROM dwd.dwd_as_qy_iou_refund_s_d\n"
                    + "                    WHERE refund_status='20090' AND dt='2018-05-15' AND a_order_no <> '12467657248'\n"
                    + "                      AND a_refund_no <> '1610230919767139947'\n"
                    + "                    GROUP BY substring(refund_time, 1, 10), a_pin )c\n"
                    + "              ON cast(a.a_pin AS string)=cast(c.a_pin AS string) AND a1.dim_day_txdate=c.refund_time\n"
                    + "  LEFT OUTER JOIN (SELECT dt, a_pin, sum(os_prcp_amt) AS os_prcp_amt\n"
                    + "                    FROM dwd.dwd_as_qy_cycle_detail_s_d\n"
                    + "                    WHERE dt>=concat(substr('2018-05-15', 1, 7), '-01') AND dt<='2018-05-15'\n"
                    + "                    GROUP BY dt, a_pin)e\n"
                    + "              ON cast(a.jd_pin AS string)=cast(e.a_pin AS string) AND a1.dim_day_txdate=e.dt\n"
                    + "  GROUP BY a1.dim_day_txdate, a.a_pin;";

    static String test_sql1 = "with \n" +
            "tmp_login as\n" +
            "(\n" +
            "    select\n" +
            "        area_code,\n" +
            "        count(*) login_count\n" +
            "    from ${APP}.dwd_start_log\n" +
            "    where dt='$do_date'\n" +
            "    group by area_code\n" +
            "),\n" +
            "tmp_op as\n" +
            "(\n" +
            "    select\n" +
            "        province_id,\n" +
            "        sum(if(date_format(create_time,'yyyy-MM-dd')='$do_date',1,0)) order_count,\n" +
            "        sum(if(date_format(create_time,'yyyy-MM-dd')='$do_date',final_total_amount,0)) order_amount,\n" +
            "        sum(if(date_format(payment_time,'yyyy-MM-dd')='$do_date',1,0)) payment_count,\n" +
            "        sum(if(date_format(payment_time,'yyyy-MM-dd')='$do_date',final_total_amount,0)) payment_amount\n" +
            "    from ${APP}.dwd_fact_order_info\n" +
            "    where (dt='$do_date' or dt=date_add('$do_date',-1))\n" +
            "    group by province_id\n" +
            ")\n" +
            "insert overwrite table ${APP}.dws_area_stats_daycount partition(dt='$do_date')\n" +
            "select\n" +
            "    pro.id,\n" +
            "    pro.province_name,\n" +
            "    --pro.area_code,\n" +
            "    pro.iso_code,\n" +
            "    pro.region_id,\n" +
            "    pro.region_name,\n" +
            "    tmp_login.area_code,\n" +
            "    nvl(tmp_login.login_count,0),\n" +
            "    nvl(tmp_op.order_count,0),\n" +
            "    nvl(tmp_op.order_amount,0.0),\n" +
            "    nvl(tmp_op.payment_count,0),\n" +
            "    nvl(tmp_op.payment_amount,0.0)\n" +
            "from ${APP}.dwd_dim_base_province pro\n" +
            "left join tmp_login on pro.area_code=tmp_login.area_code\n" +
            "left join tmp_op on pro.id=tmp_op.province_id;\n";

    public static void main(String[] args) throws UnsupportedEncodingException {

        String sql0 = "insert into dwd.dwd_hr_ts_di (a1, a2, usrid_long)\n" +
                "select \n" +
                "       depi.gsbmo as a1,\n" +
                "       depi.gsbmt as a2,\n" +
                "       depi.usrid_long\n" +
                " from table1 depi";
        String sql1 = "insert overwrite table kf_zt.account2  SELECT swjg_dm,swjgmc FROM (SELECT swjg_dm AS swjg_dm, swjgmc AS swjgmc FROM (SELECT swjg_dm,swjgmc FROM ( SELECT swjg_dm,swjgmc FROM yuangongs) table_350 UNION SELECT swjg_dm,swjgmc FROM (SELECT swjg_dm2 as swjg_dm,swjgmc2 as swjgmc FROM yuangong11 ) table_295) union_013) udf_882";

        String sql2 = "insert into  table_name_new (c1,c2) select column1,column2 from table_name_old";
        String sql3 = "with t as (select c1,c2,c3,c4 from consumer ab union select c1,c2,c3,c4 from comsumer100 ac union select c1,c2,c3,c4 from (select c1,c2,c3,c4 from abtest) a) insert into consumer_new (c4,c3,c2,c1) select t.c1,t.c2,t2.c3,t3.c4,t3.d2, t2.e1 from t, t2, (select d1 as c4,d2,d3 from t4) t3 where t.c1 = t2.c1 and t2.c1=t3.d1  ";
        String sql4 = "insert into table2 SELECT\n" +
                "    table1.col1 as 'col1',\n" +
                "\t(case \n" +
                "\t\twhen table1.col2 = 0 then '0' \n" +
                "\t\twhen table1.col2 = 1 then '1' \n" +
                "\t\twhen table1.col2 = 2 then '2' \n" +
                "\t\telse  table1.col2 end ) as 'col2',\n" +
                "\ttable1.col3 as 'col3'\n" +
                "FROM\n" +
                "\ttable1\n" +
                "WHERE table1.col3 = '1'\n";
        String sql5 = "insert into  table_name_new (c1,c2,c4) select a.column1 c1,c.column2 as c4,b.c2 from hive.table_name_old a inner join table_b b on b.id=a.id left join table_c c on c.id=b.id";
        byte[] bytes = FileUtil.readBytes("testsql/1.sql");
        String testsqls = new String(bytes, "utf-8");
        String[] sqls = testsqls.split(";");
        ListUtil.toList(sqls);

        try {
            LineageContext context = LineageSqlUtils.getInstance().handle(ListUtil.toList(sqls), DbType.hive.name());
            context.print();
        } catch (Exception e) {
            //System.out.println("不支持SQL：" + sql);
            System.out.println("错误名称：" + e.getMessage());
            e.printStackTrace();
        }


    }

}
